import logging
import os
from datetime import datetime
import pandas as pd
from flask import Flask, request, render_template, Blueprint, send_from_directory, jsonify, \
    session, make_response
from flask_caching import Cache
from flask_login import current_user, login_required
from sqlalchemy import func, case, distinct
from werkzeug.utils import secure_filename
from exts import db, csrf
from models import UserBehavior, CleanedUserBehavior, Log, UserModel

bp = Blueprint('ec', __name__, url_prefix='/')

# 初始化Flask应用
app = Flask(__name__)

# 配置上传文件夹
UPLOAD_FOLDER = 'uploads'
ALLOWED_EXTENSIONS = {'csv'}
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER


@bp.route('/download/<filename>')
def download_image(filename):
    # 记录下载图片日志
    log_behavior(session.get('user_id'), '下载图片', f'文件名: {filename}')
    return send_from_directory('static', filename, as_attachment=True)


@bp.route('/answer.html')
def answer():
    question = request.args.get('question', '')
    user = None
    if session.get('user_id'):
        user = UserModel.query.get(session['user_id'])
    return render_template('answer.html',
                           question=question,
                           now=datetime.now(),
                           user=user)  # 传递用户对象到模板


@bp.route('/')
def index():
    # 如果用户已登录，显示真实主页
    if session.get('user_id'):
        # 记录用户访问仪表盘页面的日志
        log_behavior(session.get('user_id'), '访问仪表盘')
        return render_template('index.html')
    # 未登录显示伪主页
    return render_template('pseudo_index.html')


@bp.route('/generate_daily_behavior')
def generate_daily_behavior():
    try:
        # 使用 SQLAlchemy 直接进行聚合查询
        daily_behavior = (
            db.session.query(
                func.date(CleanedUserBehavior.timestamp).label('date'),
                CleanedUserBehavior.behavior_type,
                func.count(CleanedUserBehavior.behavior_type).label('count')
            )
            .group_by('date', 'behavior_type')
            .all()
        )

        # 如果没有数据，返回空数据
        if not daily_behavior:
            return jsonify({
                'success': True,
                'labels': [],
                'datasets': []
            })

        # 将查询结果转换为 DataFrame
        data = pd.DataFrame(daily_behavior, columns=['date', 'behavior_type', 'count'])

        # 按天统计用户行为
        daily_behavior = data.pivot(index='date', columns='behavior_type', values='count').fillna(0)

        # 准备 Chart.js 所需的数据格式
        labels = daily_behavior.index.astype(str).tolist()  # 日期作为 X 轴标签
        datasets = []
        colors = [
            'rgba(255, 99, 132, 0.8)',  # 红色
            'rgba(54, 162, 235, 0.8)',  # 蓝色
            'rgba(75, 192, 192, 0.8)',  # 绿色
            'rgba(153, 102, 255, 0.8)',  # 紫色
        ]
        for i, behavior_type in enumerate(daily_behavior.columns):
            datasets.append({
                'label': behavior_type,  # 行为类型
                'data': daily_behavior[behavior_type].tolist(),  # 每天的行为数量
                'borderColor': colors[i],  # 折线颜色
                'backgroundColor': colors[i],  # 填充颜色
                'fill': False,  # 不填充折线下方区域
                'tension': 0.3  # 折线平滑度
            })

        # 返回 JSON 数据
        return jsonify({
            'success': True,
            'labels': labels,
            'datasets': datasets
        })
    except Exception as e:
        # 如果发生错误，返回错误信息
        return jsonify({
            'success': False,
            'message': str(e)
        })


@bp.route('/generate_hourly_behavior')
def generate_hourly_behavior():
    try:
        # 使用 SQLAlchemy 直接进行聚合查询
        hourly_behavior = (
            db.session.query(
                func.extract('hour', CleanedUserBehavior.timestamp).label('hour'),
                CleanedUserBehavior.behavior_type,
                func.count(CleanedUserBehavior.behavior_type).label('count')
            )
            .group_by('hour', 'behavior_type')
            .all()
        )

        # 如果没有数据，返回空数据
        if not hourly_behavior:
            return jsonify({
                'success': True,
                'labels': [],
                'datasets': []
            })

        # 将查询结果转换为 DataFrame
        data = pd.DataFrame(hourly_behavior, columns=['hour', 'behavior_type', 'count'])

        # 按小时统计用户行为
        hourly_behavior = data.pivot(index='hour', columns='behavior_type', values='count').fillna(0)

        # 准备 Chart.js 所需的数据格式
        labels = hourly_behavior.index.astype(str).tolist()  # 小时作为 X 轴标签
        datasets = []
        colors = [
            'rgba(255, 99, 132, 0.8)',  # 红色
            'rgba(54, 162, 235, 0.8)',  # 蓝色
            'rgba(75, 192, 192, 0.8)',  # 绿色
            'rgba(153, 102, 255, 0.8)',  # 紫色
        ]
        for i, behavior_type in enumerate(hourly_behavior.columns):
            datasets.append({
                'label': behavior_type,  # 行为类型
                'data': hourly_behavior[behavior_type].tolist(),  # 每小时的行为数量
                'backgroundColor': colors[i],  # 柱状图颜色
                'borderColor': colors[i],  # 折线图颜色
                'borderWidth': 1  # 边框宽度
            })

        # 返回 JSON 数据
        return jsonify({
            'success': True,
            'labels': labels,
            'datasets': datasets
        })
    except Exception as e:
        # 如果发生错误，返回错误信息
        return jsonify({
            'success': False,
            'message': str(e)
        })


@bp.route('/generate_behavior_distribution')
def generate_behavior_distribution():
    try:
        # 使用 SQLAlchemy 直接进行聚合查询
        behavior_counts = (
            db.session.query(
                CleanedUserBehavior.behavior_type,
                func.count(CleanedUserBehavior.behavior_type).label('count')
            )
            .group_by(CleanedUserBehavior.behavior_type)
            .all()
        )

        # 如果没有数据，返回空数据
        if not behavior_counts:
            return jsonify({
                'success': True,
                'labels': [],
                'data': []
            })

        # 定义行为类型的顺序和颜色，使用中文标签
        behavior_order = ['buy', 'cart', 'fav', 'pv']  # 原始顺序
        behavior_labels = ['购买', '加入购物车', '收藏', '点击']  # 对应的中文标签
        colors = [
            'rgba(255, 99, 132, 0.8)',  # buy: 红色
            'rgba(54, 162, 235, 0.8)',  # cart: 蓝色
            'rgba(75, 192, 192, 0.8)',  # fav: 绿色
            'rgba(153, 102, 255, 0.8)',  # pv: 紫色
        ]

        # 按定义顺序排序行为类型
        behavior_counts_dict = {row.behavior_type: row.count for row in behavior_counts}
        data = [behavior_counts_dict.get(behavior, 0) for behavior in behavior_order]

        # 返回 JSON 数据，使用中文标签
        return jsonify({
            'success': True,
            'labels': behavior_labels,  # 使用中文标签
            'data': data,
            'colors': colors
        })
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        })


@bp.route('/generate_top10_items_pv')
def generate_top10_items_pv():
    try:
        # 首先获取总点击量
        total_pv = db.session.query(
            func.count(CleanedUserBehavior.id)
        ).filter(
            CleanedUserBehavior.behavior_type == 'pv'
        ).scalar() or 1  # 避免除以0

        # 查询点击量Top10的商品
        top10_pv_items = (
            db.session.query(
                CleanedUserBehavior.item_id,
                func.count(CleanedUserBehavior.item_id).label('pv_count'),
                (func.count(CleanedUserBehavior.item_id) / total_pv * 10000).label('pv_rate_per_10k')  # 改为万分比显示
            )
            .filter(CleanedUserBehavior.behavior_type == 'pv')
            .group_by(CleanedUserBehavior.item_id)
            .order_by(func.count(CleanedUserBehavior.item_id).desc())
            .limit(10)
            .all()
        )

        # 准备数据
        labels = [f"商品{item.item_id}" for item in top10_pv_items]
        pv_counts = [item.pv_count for item in top10_pv_items]
        pv_rates = [float(item.pv_rate_per_10k) for item in top10_pv_items]  # 万分比数据

        return jsonify({
            'success': True,
            'labels': labels,
            'pv_counts': pv_counts,
            'pv_rates': pv_rates,
            'rate_unit': '%'
        })
    except Exception as e:
        import traceback
        traceback.print_exc()
        return jsonify({
            'success': False,
            'message': str(e)
        })


@bp.route('/generate_top10_items_buy')
def generate_top10_items_buy():
    try:
        # 查询购买量Top10的商品及其点击量
        top10_buy_items = (
            db.session.query(
                CleanedUserBehavior.item_id,
                func.sum(case((CleanedUserBehavior.behavior_type == 'buy', 1), else_=0)).label('buy_count'),
                func.count(CleanedUserBehavior.item_id).label('pv_count'),
                (func.sum(case((CleanedUserBehavior.behavior_type == 'buy', 1), else_=0)) /
                 func.count(CleanedUserBehavior.item_id) * 100).label('buy_rate')
            )
            .group_by(CleanedUserBehavior.item_id)
            .order_by(func.sum(case((CleanedUserBehavior.behavior_type == 'buy', 1), else_=0)).desc())
            .limit(10)
            .all()
        )

        # 准备数据
        labels = [f"商品{item.item_id}" for item in top10_buy_items]
        buy_counts = [item.buy_count for item in top10_buy_items]
        buy_rates = [float(item.buy_rate) for item in top10_buy_items]

        return jsonify({
            'success': True,
            'labels': labels,
            'buy_counts': buy_counts,
            'buy_rates': buy_rates
        })
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        })


@bp.route('/generate_top10_categories_buy')
def generate_top10_categories_buy():
    try:
        # 查询购买量Top10的商品类别及其点击量
        top10_buy_categories = (
            db.session.query(
                CleanedUserBehavior.category_id,
                func.sum(case((CleanedUserBehavior.behavior_type == 'buy', 1), else_=0)).label('buy_count'),
                func.count(CleanedUserBehavior.category_id).label('pv_count'),
                (func.sum(case((CleanedUserBehavior.behavior_type == 'buy', 1), else_=0)) /
                 func.count(CleanedUserBehavior.category_id) * 100).label('buy_rate')
            )
            .group_by(CleanedUserBehavior.category_id)
            .order_by(func.sum(case((CleanedUserBehavior.behavior_type == 'buy', 1), else_=0)).desc())
            .limit(10)
            .all()
        )

        # 准备数据
        labels = [f"类别{item.category_id}" for item in top10_buy_categories]
        buy_counts = [item.buy_count for item in top10_buy_categories]
        buy_rates = [float(item.buy_rate) for item in top10_buy_categories]

        return jsonify({
            'success': True,
            'labels': labels,
            'buy_counts': buy_counts,
            'buy_rates': buy_rates
        })
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        })


@bp.route('/generate_conversion_rates')
def generate_conversion_rates():
    try:
        # 获取各行为总量
        behavior_counts = db.session.query(
            CleanedUserBehavior.behavior_type,
            func.count(CleanedUserBehavior.id).label('count')
        ).group_by(CleanedUserBehavior.behavior_type).all()

        counts = {b.behavior_type: b.count for b in behavior_counts}

        total_pv = counts.get('pv', 1)
        total_cart = counts.get('cart', 1)
        total_fav = counts.get('fav', 1)
        total_buy = counts.get('buy', 1)

        # 计算各阶段转化率
        pv_to_cart = (total_cart / total_pv) * 100
        pv_to_fav = (total_fav / total_pv) * 100
        cart_to_buy = (total_buy / total_cart) * 100
        fav_to_buy = (total_buy / total_fav) * 100

        # 计算路径转化率
        pv_cart_buy = pv_to_cart * cart_to_buy / 100
        pv_fav_buy = pv_to_fav * fav_to_buy / 100

        return jsonify({
            'success': True,
            'labels': [
                '点击→加购→购买',
                '点击→收藏→购买',
                '加购→购买'
            ],
            'rates': [
                pv_cart_buy,
                pv_fav_buy,
                cart_to_buy
            ],
            'stage_rates': [  # 各阶段转化率
                pv_to_cart,
                pv_to_fav,
                cart_to_buy,
                fav_to_buy
            ],
            'counts': {  # 各行为数量
                'pv': total_pv,
                'cart': total_cart,
                'fav': total_fav,
                'buy': total_buy
            },
            'colors': [
                'rgba(54, 162, 235, 0.8)',  # 蓝色
                'rgba(255, 206, 86, 0.8)',  # 黄色
                'rgba(75, 192, 192, 0.8)'  # 绿色
            ]
        })
    except Exception as e:
        return jsonify({
            'success': False,
            'message': str(e)
        })


@bp.route('/visual2')
def visual2():
    # 记录用户访问预测与推荐页面的日志
    log_behavior(session.get('user_id'), '访问预测与推荐页面')
    return render_template("visual2.html")


@bp.route('/user')
@login_required  # 确保用户已登录
def user():
    # 记录用户访问个人中心页面的日志
    log_behavior(current_user.id, '访问个人中心页面')
    return render_template("user.html")


# 配置日志
# logging.basicConfig(filename='app.log', level=logging.INFO)


def log_behavior(user_id, behavior, details=None):
    """记录用户操作日志"""
    # 获取当前登录用户的用户名
    if user_id:
        user = UserModel.query.get(user_id)
        username = user.username if user else '默认用户'
    else:
        username = '默认用户'

    # 记录日志
    log = Log(user_id=user_id, behavior=behavior, details=details)
    db.session.add(log)
    db.session.commit()
    logging.info(f"用户 {username} 执行了操作: {behavior}. 详情: {details}")


@bp.route('/visual')
def visual():
    # 记录用户访问操作日志
    log_behavior(session.get('user_id'), '访问操作日志页面')
    return render_template("visual.html")


@bp.route('/log', methods=['GET'])
def get_logs():
    # 获取分页参数
    page = request.args.get('page', 1, type=int)
    per_page = 20  # 每页显示20条记录

    # 分页查询日志数据
    logs_pagination = Log.query.order_by(Log.timestamp.desc()).paginate(page=page, per_page=per_page, error_out=False)
    logs = logs_pagination.items

    # 将日志数据转换为 JSON 格式
    logs_json = []
    for log in logs:
        if log.user_id:
            user = UserModel.query.get(log.user_id)
            username = user.username if user else '未知用户'
        else:
            username = '系统'

        logs_json.append({
            'timestamp': log.timestamp.strftime('%Y-%m-%d %H:%M:%S'),
            'behavior': log.behavior,
            'user': username,
            'details': log.details
        })

    return jsonify(logs_json)


@bp.route('/download_logs', methods=['GET'])
def download_logs():
    # 下载日志文件
    logs = Log.query.all()
    log_data = [f"{log.timestamp}, {log.user_id}, {log.behavior}, {log.details}\n" for log in logs]
    with open('logs.txt', 'w') as f:
        f.writelines(log_data)
    return send_from_directory('.', 'logs.txt', as_attachment=True)


# 配置缓存
cache = Cache(config={'CACHE_TYPE': 'simple'})
cache.init_app(app)


# 在 raw_data 路由中添加统计数据
@bp.route('/raw_data')
@cache.cached(timeout=60, query_string=True)  # 缓存60秒，区分不同查询参数
def raw_data():
    # 获取筛选参数
    user_id = request.args.get('user_id')
    item_id = request.args.get('item_id')
    behavior_type = request.args.get('behavior_type')

    # 构建基础查询 - 只选择需要的列
    query = db.session.query(
        UserBehavior.id,
        UserBehavior.user_id,
        UserBehavior.item_id,
        UserBehavior.category_id,
        UserBehavior.behavior_type,
        UserBehavior.timestamp
    )

    # 应用筛选条件
    if user_id:
        query = query.filter(UserBehavior.user_id == user_id)
    if item_id:
        query = query.filter(UserBehavior.item_id == item_id)
    if behavior_type:
        query = query.filter(UserBehavior.behavior_type == behavior_type)

    # 获取分页参数
    page = request.args.get('page', 1, type=int)
    per_page = 20  # 每页显示20条数据

    # 使用更高效的分页方法
    raw_data_pagination = query.paginate(page=page, per_page=per_page, error_out=False)

    # 预加载关联数据（如果有）
    # 例如：query = query.options(joinedload(UserBehavior.some_relation))

    # 获取统计数据 - 使用更高效的查询方式
    stats_query = db.session.query(
        func.count(distinct(UserBehavior.user_id)).label('user_count'),
        func.count(distinct(UserBehavior.item_id)).label('item_count'),
        func.count(distinct(UserBehavior.category_id)).label('category_count'),
        func.count(UserBehavior.id).label('total_records')
    )

    # 应用相同的筛选条件到统计查询
    if user_id:
        stats_query = stats_query.filter(UserBehavior.user_id == user_id)
    if item_id:
        stats_query = stats_query.filter(UserBehavior.item_id == item_id)
    if behavior_type:
        stats_query = stats_query.filter(UserBehavior.behavior_type == behavior_type)

    stats = stats_query.one()

    # 将分页信息和统计数据传递给模板
    return render_template('raw_data.html',
                           raw_data=raw_data_pagination.items,
                           pagination=raw_data_pagination,
                           user_count=stats.user_count,
                           item_count=stats.item_count,
                           category_count=stats.category_count,
                           total_records=stats.total_records)


# 添加获取单条记录详情的路由
@bp.route('/get_record_details')
def get_record_details():
    record_id = request.args.get('id')
    record = UserBehavior.query.get(record_id)

    if record:
        return jsonify({
            'id': record.id,
            'user_id': record.user_id,
            'item_id': record.item_id,
            'category_id': record.category_id,
            'behavior_type': record.behavior_type,
            'timestamp': record.timestamp.strftime('%Y-%m-%d %H:%M:%S')
        })
    else:
        return jsonify({'error': 'Record not found'}), 404


# 添加导出数据路由
@bp.route('/export_data')
def export_data():
    # 获取筛选参数
    user_id = request.args.get('user_id')
    item_id = request.args.get('item_id')
    behavior_type = request.args.get('behavior_type')

    # 构建查询
    query = UserBehavior.query

    if user_id:
        query = query.filter(UserBehavior.user_id == user_id)
    if item_id:
        query = query.filter(UserBehavior.item_id == item_id)
    if behavior_type:
        query = query.filter(UserBehavior.behavior_type == behavior_type)

    # 获取所有数据
    data = query.all()

    # 创建CSV文件
    import csv
    from io import StringIO

    output = StringIO()
    writer = csv.writer(output)

    # 写入标题行
    writer.writerow(['用户ID', '商品ID', '商品类目ID', '行为类型', '时间戳'])

    # 写入数据行
    for row in data:
        writer.writerow([
            row.user_id,
            row.item_id,
            row.category_id,
            row.behavior_type,
            row.timestamp.strftime('%Y-%m-%d %H:%M:%S')
        ])

    # 准备响应
    output.seek(0)
    response = make_response(output.getvalue())
    response.headers['Content-Disposition'] = 'attachment; filename=user_behavior_data.csv'
    response.headers['Content-type'] = 'text/csv'

    # 记录导出日志
    log_behavior(session.get('user_id'), '导出数据',
                 f'筛选条件: 用户ID={user_id}, 商品ID={item_id}, 行为类型={behavior_type}')

    return response


# 确保上传文件夹存在
if not os.path.exists(UPLOAD_FOLDER):
    os.makedirs(UPLOAD_FOLDER)


def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


@bp.route('/upload', methods=['POST'])
def upload_file():
    if 'file' not in request.files:
        return jsonify({'success': False, 'message': 'No file part'})

    file = request.files['file']

    if file.filename == '':
        return jsonify({'success': False, 'message': 'No selected file'})

    if file and allowed_file(file.filename):
        # 清空 uploads 文件夹
        for filename in os.listdir(app.config['UPLOAD_FOLDER']):
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
            try:
                if os.path.isfile(file_path) or os.path.islink(file_path):
                    os.unlink(file_path)
            except Exception as e:
                print(f'Failed to delete {file_path}. Reason: {e}')

        # 保存新文件
        filename = secure_filename(file.filename)
        file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
        file.save(file_path)

        try:
            # 分块读取CSV文件
            chunksize = 10 ** 6  # 每次读取1百万行
            chunks = pd.read_csv(file_path, header=None, chunksize=chunksize)

            # 清空数据库中的旧数据
            UserBehavior.query.delete()
            CleanedUserBehavior.query.delete()
            db.session.commit()

            for chunk in chunks:
                chunk.columns = ['user_id', 'item_id', 'category_id', 'behavior_type', 'timestamp']

                # 存储原始数据到 UserBehavior 表
                for index, row in chunk.iterrows():
                    timestamp = datetime.fromtimestamp(row['timestamp'])
                    user_behavior = UserBehavior(
                        user_id=row['user_id'],
                        item_id=row['item_id'],
                        category_id=row['category_id'],
                        behavior_type=row['behavior_type'],
                        timestamp=timestamp
                    )
                    db.session.add(user_behavior)
                db.session.commit()

                # 数据清洗逻辑
                cleaned_data, error_data = clean_csv_data(chunk)

                # 存储清洗后的数据到 CleanedUserBehavior 表
                for data in cleaned_data:
                    cleaned_behavior = CleanedUserBehavior(
                        user_id=data['user_id'],
                        item_id=data['item_id'],
                        category_id=data['category_id'],
                        behavior_type=data['behavior_type'],
                        timestamp=datetime.strptime(data['timestamp'], '%Y-%m-%d %H:%M:%S')
                    )
                    db.session.add(cleaned_behavior)
                db.session.commit()

            # 记录文件上传日志
            log_behavior(session.get('user_id'), '上传文件', f'文件名: {filename}')
            return jsonify({'success': True, 'message': '文件上传成功，数据已导入数据库'})
        except Exception as e:
            db.session.rollback()
            # 记录文件上传失败日志
            log_behavior(session.get('user_id'), '上传文件失败', f'错误信息: {str(e)}')
            return jsonify({'success': False, 'message': f'文件上传失败: {str(e)}'})
    else:
        # 记录文件类型不支持日志
        log_behavior(session.get('user_id'), '文件类型不支持', '只允许上传 CSV 文件')
        return jsonify({'success': False, 'message': '只允许上传 CSV 文件'})


def clean_csv_data(df):
    """
    数据清洗逻辑：清洗 CSV 文件中的数据
    """
    cleaned_data = []
    error_data = []

    # 示例清洗逻辑：去除行为类型为空的记录
    for index, row in df.iterrows():
        if pd.notna(row['behavior_type']):  # 检查 behavior_type 是否为空
            # 将 Unix 时间戳转换为可读的日期时间格式
            timestamp = datetime.fromtimestamp(row['timestamp']).strftime('%Y-%m-%d %H:%M:%S')
            cleaned_data.append({
                'user_id': row['user_id'],
                'item_id': row['item_id'],
                'category_id': row['category_id'],
                'behavior_type': row['behavior_type'],
                'timestamp': timestamp  # 使用转换后的时间戳
            })
        else:
            error_data.append({
                'user_id': row['user_id'],
                'item_id': row['item_id'],
                'category_id': row['category_id'],
                'behavior_type': row['behavior_type'],
                'timestamp': datetime.fromtimestamp(row['timestamp']).strftime('%Y-%m-%d %H:%M:%S')
            })

    return cleaned_data, error_data


@bp.route('/clean_data', methods=['POST'])
def clean_database_data():
    try:
        # 清空 CleanedUserBehavior 表
        CleanedUserBehavior.query.delete()
        db.session.commit()

        # 分块处理数据
        chunk_size = 10000  # 每次处理 10000 条数据
        offset = 0
        error_data = []

        while True:
            raw_data = UserBehavior.query.offset(offset).limit(chunk_size).all()
            if not raw_data:
                break

            cleaned_data = []
            for row in raw_data:
                if row.behavior_type:  # 示例清洗逻辑：去除行为类型为空的记录
                    cleaned_data.append({
                        'user_id': row.user_id,
                        'item_id': row.item_id,
                        'category_id': row.category_id,
                        'behavior_type': row.behavior_type,
                        'timestamp': row.timestamp.strftime('%Y-%m-%d %H:%M:%S')
                    })
                else:
                    error_data.append({
                        'user_id': row.user_id,
                        'item_id': row.item_id,
                        'category_id': row.category_id,
                        'behavior_type': row.behavior_type,
                        'timestamp': row.timestamp.strftime('%Y-%m-%d %H:%M:%S')
                    })

            # 批量插入清洗后的数据
            if cleaned_data:
                db.session.bulk_insert_mappings(CleanedUserBehavior, cleaned_data)
                db.session.commit()

            offset += chunk_size

        # 记录数据清洗日志
        log_behavior(session.get('user_id'), '数据清洗', '清洗了数据，并将清洗后的数据存入数据库')

        # 如果没有错误数据，返回特定消息
        if not error_data:
            return jsonify({'success': True, 'message': '数据清洗完成，未发现错误数据'})
        else:
            return jsonify(error_data)
    except Exception as e:
        db.session.rollback()
        # 记录数据清洗失败日志
        log_behavior(session.get('user_id'), '数据清洗失败', f'错误信息: {str(e)}')
        return jsonify({'success': False, 'message': f'数据清洗失败: {str(e)}'})
